package com.apes.fn.mdm.purchase;

import com.apes.framework.jpa.repository.CustomRepository;
import org.elasticsearch.common.recycler.Recycler;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface PurchasingGroupRepository extends CustomRepository<PurchasingGroup, String> {

    //------------------------------------门店-----------------------------------------------

    @Query(value = "SELECT * FROM MAS_DEPT WHERE ID = :deptId OR NAME LIKE '%' || :deptName || '%' ", nativeQuery = true)
    List<Map> queryLikePurchasingGroupDept(@Param("deptId") String deptId, @Param("deptName") String deptName);

//    PURCHASING_ID,
    @Query(value = "SELECT DISTINCT DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME FROM CM_PURCHASING_DEPT A WHERE DEPT_ID = :deptId", nativeQuery = true)
    List<Map> queryPurchasingGroupDept(@Param("deptId") String deptId);

//     A.PURCHASING_ID,
    @Query(value = "SELECT DISTINCT A.SUPPLIER_ID, (SELECT J.NAME FROM MAS_PARTY J, MAS_PARTY_ROLE K WHERE J.ID = K.PARTY_ID AND K.ID = A.SUPPLIER_ID) SUPPLIER_NAME FROM CM_PURCHASING_SUPPLIER A WHERE EXISTS (SELECT 1 FROM CM_PURCHASING_DEPT WHERE PURCHASING_ID = A.PURCHASING_ID AND DEPT_ID = :deptId)", nativeQuery = true)
    List<Map> accordingToDeptGetSupplier(@Param("deptId") String deptId);

    @Query(value = "SELECT ID, NAME, VALID, VIRTUAL FROM CM_PURCHASING_GROUP A WHERE EXISTS (SELECT 1 FROM CM_PURCHASING_DEPT WHERE PURCHASING_ID = A.ID AND DEPT_ID = :deptId)", nativeQuery = true)
    List<Map> accordingToDeptGetPurchasingGroup(@Param("deptId") String deptId);

    @Query(value = "SELECT DISTINCT B.SUPPLIER_ID FROM CM_PURCHASING_DEPT A JOIN CM_PURCHASING_SUPPLIER B ON A.PURCHASING_ID = B.PURCHASING_ID WHERE A.DEPT_ID = :dept_id", nativeQuery = true)
    List<String> accordingToDeptGetSupplierId(@Param("dept_id") String deptId);


    //------------------------------------供应商-----------------------------------------------

    @Query(value = "" +
            "SELECT B.ID, NAME " +
            "  FROM MAS_PARTY A, MAS_PARTY_ROLE B " +
            " WHERE A.ID = B.PARTY_ID " +
            "   AND B.ROLE_ID = 'Supplier' AND (B.ID = :supplierId OR A.NAME LIKE '%' || :supplierName || '%') ", nativeQuery = true)
    List<Map> queryLikeSupplierInfo(@Param("supplierId") String supplierId, @Param("supplierName") String supplierName);

    @Query(value = "SELECT DISTINCT SUPPLIER_ID, (SELECT J.NAME FROM MAS_PARTY J, MAS_PARTY_ROLE K WHERE J.ID = K.PARTY_ID AND K.ID = A.SUPPLIER_ID) SUPPLIER_NAME FROM CM_PURCHASING_SUPPLIER A WHERE SUPPLIER_ID = :supplierId", nativeQuery = true)
    List<Map> queryPurchasingGroupSupplier(@Param("supplierId") String supplierId);

    @Query(value = "SELECT DISTINCT A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME FROM CM_PURCHASING_DEPT A WHERE EXISTS (SELECT 1 FROM CM_PURCHASING_SUPPLIER WHERE PURCHASING_ID = A.PURCHASING_ID AND SUPPLIER_ID = :supplierId)", nativeQuery = true)
    List<Map> accordingToSupplierGetDept(@Param("supplierId") String supplierId);

    @Query(value = "SELECT ID, NAME, VALID FROM CM_PURCHASING_GROUP A WHERE EXISTS (SELECT 1 FROM CM_PURCHASING_SUPPLIER WHERE PURCHASING_ID = A.ID AND SUPPLIER_ID = :supplierId)", nativeQuery = true)
    List<Map> accordingToSupplierGetPurchasingGroup(@Param("supplierId") String supplierId);

    //------------------------------------采购组-----------------------------------------------

    @Query(value = "SELECT ID, NAME, VALID FROM CM_PURCHASING_GROUP WHERE ID = :id OR NAME LIKE '%' || :name || '%' ", nativeQuery = true)
    List<Map> queryLikePurchasingGroup(@Param("id") String id, @Param("name") String name);

    @Query(value = "SELECT ID, NAME, VALID FROM CM_PURCHASING_GROUP WHERE ID = :id", nativeQuery = true)
    List<Map> queryPurchasingGroup(@Param("id") String id);

    @Query(value = "SELECT DISTINCT A.SUPPLIER_ID, (SELECT J.NAME FROM MAS_PARTY J, MAS_PARTY_ROLE K WHERE J.ID = K.PARTY_ID AND K.ID = A.SUPPLIER_ID) SUPPLIER_NAME FROM CM_PURCHASING_SUPPLIER A WHERE EXISTS (SELECT 1 FROM CM_PURCHASING_GROUP WHERE ID = A.PURCHASING_ID AND ID = :id)", nativeQuery = true)
    List<Map> accordingPurchasingGroupGetSupplier(@Param("id") String id);

    @Query(value = "SELECT DISTINCT A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME FROM CM_PURCHASING_DEPT A WHERE EXISTS (SELECT 1 FROM CM_PURCHASING_GROUP WHERE ID = A.PURCHASING_ID AND ID = :id)", nativeQuery = true)
    List<Map> accordingPurchasingGroupGetDept(@Param("id") String id);

    @Query(value = "SELECT DISTINCT A.DEPT_ID DEPT_NAME FROM CM_PURCHASING_DEPT A WHERE A.PURCHASING_ID = (SELECT B.ID FROM CM_PURCHASING_GROUP B WHERE NAME = :name)", nativeQuery = true)
    List<String> accordingPurchasingGroupGetDeptId(@Param("name") String name);

    @Query(value = "SELECT * FROM CM_PURCHASING_SUPPLIER WHERE PURCHASING_ID = :purchasingId AND SUPPLIER_ID IN (:supplierId)", nativeQuery = true)
    List<Map> getPurchasingSupplier(@Param("purchasingId") String purchasingId, @Param("supplierId") List supplierId);

    @Query(value = "SELECT * FROM CM_PURCHASING_OPERABLE WHERE PURCHASING_ID = :purchaseingId", nativeQuery = true)
    List<Map> getPurchaseGroupOperator(@Param("purchaseingId") String purchaseingId);

    @Query(value = "select a.* from cm_purchasing_group a WHERE a.id in \n" +
            "(SELECT b.purchasing_id FROM cm_purchasing_dept b join cm_purchasing_supplier c on b.purchasing_id = c.purchasing_id \n" +
            "WHERE b.dept_id = :dept and c.supplier_id = :supplier)", nativeQuery = true)
    List<PurchasingGroup> findDeptAndSupplier(String dept, String supplier);


    @Query(value = "" +
            "SELECT A.ID, B.DEPT_ID " +
            "  FROM CM_PURCHASING_GROUP A, CM_PURCHASING_DEPT B " +
            " WHERE A.ID = B.PURCHASING_ID " +
            "   AND A.ID = ?1 " +
            "   AND B.DEPT_ID = ?2 ", nativeQuery = true)
    List<Map> accordPurchaseGroupIdAndDeptIdFind(String purchaseingId, String deptId);

    @Query(value = "SELECT a.PURCHASING_ID FROM cm_purchasing_supplier A ,cm_purchasing_dept B WHERE A.PURCHASING_ID = B.PURCHASING_ID \n" +
            "AND  A.SUPPLIER_ID  =:s_id AND B.dept_id =:d_id", nativeQuery = true)
    String getSupplierIdAndDeptId(@Param("s_id") String s_id,@Param("d_id") String d_id);



}

















